package Dao;
import bean.Doctor;
import bean.check;
import bean.doctor_schedule;
import bean.schedule;

import java.util.ArrayList;
import java.util.List;

public class scheduleDao extends BaseDao{
    doctorDaoImpl doctorDao = new doctorDaoImpl();

    //查询医生全部排班表
    public List<doctor_schedule> querySchedules() throws Exception {
        String sql = "SELECT sid,doctor_name,day_of_week,ampm,appointment_count,status,booked_count,date FROM doctor_schedule";//先获取医生排班表信息，后续再通过医生名字查找医生封装为一个医生排班List集合
        List<schedule> schedules = queryForList(schedule.class, sql);
        List<doctor_schedule> doctorScheduleList = new ArrayList<>();
        for (schedule schedule : schedules) {
            Doctor doctor = doctorDao.queryDoctorByName(schedule.getDoctor_name());//通过医生名字获取医生对象
            doctor_schedule doctor_schedule = new doctor_schedule(schedule.getSid(),schedule.getDoctor_name(), schedule.getDay_of_week(), doctor.getOffice(),doctor.getRoom(),schedule.getAmpm(),schedule.getAppointment_count(),schedule.getStatus(),schedule.getBooked_count(),doctor.getDid(),schedule.getDate());
            doctorScheduleList.add(doctor_schedule);
        }
        return doctorScheduleList;
    }

//    上述SQL语句创建一个名为medicine的药品表，其中id字段为自增主键，name为药品名称，
//    code为药品编码，category为药品分类，specification为药品规格，
//    producer为药品产地，price为药品价格，quantity为库存数量。药品表中的每一行代表一个药品。

    public List<doctor_schedule> queryScheduleByName(String name) throws Exception {
        String sql = "SELECT sid,doctor_name,day_of_week,ampm,appointment_count,status,booked_count,date FROM doctor_schedule WHERE doctor_name = ?";
        List<schedule> schedules = queryForList(schedule.class, sql,name);//通过医生名字获取一周的排班信息
        List<doctor_schedule> doctorScheduleList = new ArrayList<>();
        for (schedule schedule : schedules) {
            Doctor doctor = doctorDao.queryDoctorByName(schedule.getDoctor_name());//通过医生名字获取医生对象
            doctor_schedule doctor_schedule = new doctor_schedule(schedule.getSid(),schedule.getDoctor_name(), schedule.getDay_of_week(), doctor.getOffice(),doctor.getRoom(),schedule.getAmpm(),schedule.getAppointment_count(),schedule.getStatus(),schedule.getBooked_count(),doctor.getDid(),schedule.getDate());
            doctorScheduleList.add(doctor_schedule);
        }
        return doctorScheduleList;
    }
    public doctor_schedule queryScheduleByNameAndWeek(String name,String week) throws Exception {
        String sql = "SELECT sid,doctor_name,day_of_week,ampm,appointment_count,status,booked_count,date FROM doctor_schedule WHERE doctor_name = ? AND day_of_week = ?";
        schedule schedule = queryForOne(schedule.class, sql,name,week);//通过医生名字获取一周的排班信息
        Doctor doctor = doctorDao.queryDoctorByName(schedule.getDoctor_name());//通过医生名字获取医生对象
        doctor_schedule doctor_schedule = new doctor_schedule(schedule.getSid(),schedule.getDoctor_name(), schedule.getDay_of_week(), doctor.getOffice(),doctor.getRoom(),schedule.getAmpm(),schedule.getAppointment_count(),schedule.getStatus(),schedule.getBooked_count(),doctor.getDid(),schedule.getDate());
        return doctor_schedule;
    }

    public List<doctor_schedule> querySchedulesByWeek(String week) throws Exception {
        String sql = "SELECT sid,doctor_name,day_of_week,ampm,appointment_count,status,booked_count,date FROM doctor_schedule WHERE day_of_week =?";
        List<schedule> schedules = queryForList(schedule.class, sql,week);
        List<doctor_schedule> doctorScheduleList = new ArrayList<>();
        for (schedule schedule : schedules) {
            Doctor doctor = doctorDao.queryDoctorByName(schedule.getDoctor_name());//通过医生名字获取医生对象
            doctor_schedule doctor_schedule = new doctor_schedule(schedule.getSid(),schedule.getDoctor_name(), schedule.getDay_of_week(), doctor.getOffice(),doctor.getRoom(),schedule.getAmpm(),schedule.getAppointment_count(),schedule.getStatus(),schedule.getBooked_count(),doctor.getDid(),schedule.getDate());
            doctorScheduleList.add(doctor_schedule);
        }
        return doctorScheduleList;
    }


    public int addSchedule(schedule schedule) {
        String sql = "INSERT INTO doctor_schedule (doctor_name,day_of_week,ampm,appointment_count,status,booked_count,date) VALUES(?,?,?,?,?,?,?)";
        try {
            return update(sql,schedule.getDoctor_name(),schedule.getDay_of_week(),schedule.getAmpm(),schedule.getAppointment_count(),schedule.getStatus(),schedule.getBooked_count(),schedule.getDate());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public int appointmentSchedule(schedule schedule) {
        String sql = "UPDATE doctor_schedule SET booked_count = booked_count + 1 WHERE doctor_name = ? AND date = ? AND ampm = ? ";
        try {
            return update(sql,schedule.getDoctor_name(),schedule.getDate(),schedule.getAmpm());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public String queryStatusByNameAndWeek(String name,String week) throws Exception {
        String sql = "SELECT status FROM doctor_schedule WHERE doctor_name = ? AND day_of_week = ?";
        String status = (String) queryForSingleValue(sql, name, week);
        return status;
    }


}


